﻿CREATE PROCEDURE [dbo].[spCreateOrdersForSequences] ( @OwnerID int, @AsOf smalldatetime )
AS
	set nocount on
	declare @EndOfDay smalldatetime
	set @EndOfDay = DATEADD(minute, 1439, floor(cast(@AsOf as float)))

	-- Cancel sequences in which the most recent order has been canceled.
	update T2 set SequenceStatusCode = 'X', CanceledReasonID = T4.CanceledReasonID, CanceledDate = GETDATE()
	from tblSequenceOrderStep T1
		join tblSequenceOrder T2 on T1.SequenceOrderID = T2.SequenceOrderID
		join tblOrderHeader T4 on T1.LastOrderID = T4.OrderID
	where T4.CanceledReasonID is not null
		and T2.SequenceStatusCode = 'A'
		and T2.OwnerID = @OwnerID

	-- Select the sequences which need to have orders created for them
	declare SequenceOrderStepCursor cursor for 
		select T1.SequenceOrderStepID, T1.RecurrenceCount, T1.CurrentCount, T1.SequenceOrderID
		from tblSequenceOrderStep T1
			join tblSequenceOrder T2 on T1.SequenceOrderID = T2.SequenceOrderID
			join tblCustomer T3 on T2.CustomerID = T3.CustomerID
			left outer join tblOrderHeader T4 on T2.OrderID = T4.OrderID
		where T1.NextTriggerDate <= @EndOfDay
			and T2.SequenceStatusCode = 'A'
			-- Make sure the original order has shipped
			and coalesce(T4.OrderStatusCode, 'S') in ( 'S', 'T' )
			-- Make sure all subsequent continuity orders have shipped.
			and not exists ( select 1 from tblOrderHeader oh2
					join tblSequenceOrderStep sos2 on oh2.SequenceOrderStepID = sos2.SequenceOrderStepID
				where sos2.SequenceOrderID = T2.SequenceOrderID
					and oh2.OrderStatusCode not in ( 'S', 'E' ))
			and T3.OwnerID = @OwnerID
	open SequenceOrderStepCursor
	declare @SequenceOrderStepID int
	declare @SequenceOrderID int
	declare @RecurrenceCount int
	declare @CurrentCount int
	fetch next from SequenceOrderStepCursor into @SequenceOrderStepID, @RecurrenceCount, @CurrentCount, @SequenceOrderID
	while @@FETCH_STATUS = 0
	begin
		declare @OrderID int

		-- Create the new order
		insert into tblOrderHeader( CustomerID, 
			OrderDate, 
			ProjShipDate, 
			ProductTotal,
			Shipping,
			Taxes,
			OrderTotal,
			ImportDate,
			AccountID,
			ShipTo_PersonID,
			ShipTo_CompanyName,
			ShipTo_AddressID,
			OrderSourceID,
			OrderOriginCode,
			OrderStatusCode,
			PreferredCarrierCode,
			OwnerID,
			SequenceOrderStepID, 
			SequenceCount )
		select T1.CustomerID,
			GETDATE(),
			GETDATE(),
			0,
			T4.Shipping,
			0,
			0,
			GETDATE(), -- ImpoortDate
			T1.AccountID,
			T1.ShipTo_PersonID,
			T1.ShipTo_CompanyName,
			T1.ShipTo_AddressID,
			T2.OrderSourceID, -- Link the continuity source to the original order source
			'C', -- Continuity origin
			'P', -- Pending
			coalesce(T3.PreferredCarrierCode, own.PreferredCarrierCode),
			T1.OwnerID,
			@SequenceOrderStepID,
			@CurrentCount
		from tblSequenceOrder T1
			left outer join tblOrderHeader T2 on T1.OrderID = T2.OrderID
			left outer join tblSequence T3 on T1.SequenceID = T3.SequenceID
			join tblSequenceOrderStep T4 on T1.SequenceOrderID = T4.SequenceOrderID
			join tblOwner own on T1.OwnerID = own.OwnerID
		where T4.SequenceOrderStepID = @SequenceOrderStepID

		set @OrderID = SCOPE_IDENTITY()
		insert into tblOrderDetail ( OrderID, ProductID, Qty, UnitPrice, UnitShipping, ImportDate, Discount, PreferredCarrierCode )
		select @OrderID,
			T2.ProductID,
			T2.Qty,
			T2.UnitPrice,
			0,
			GETDATE(),
			T2.Discount,
			T4.PreferredCarrierCode
		from tblSequenceOrderStep T1
			join tblSequenceOrderStepProduct T2 on T1.SequenceOrderStepID = T2.SequenceOrderStepID
			join tblProduct T4 on T2.ProductID = T4.ProductID
		where T1.SequenceOrderStepID = @SequenceOrderStepID

		-- Calculate the order totals
		exec spCalcOrderTotals @OrderID

		-- Move the sequence to the next step
		if @CurrentCount >= @RecurrenceCount and @RecurrenceCount <> 0 
		begin
			-- Move to the next step in the sequence
			update T2 set NextTriggerDate = DATEADD( day, T2.Delay, getdate()), CurrentCount = 1
			from tblSequenceOrderStep T1
				join tblSequenceOrderStep T2 on T2.PrevSequenceOrderStepID = T1.SequenceOrderStepID
			where T1.SequenceOrderStepID = @SequenceOrderStepID

			update tblSequenceOrderStep set NextTriggerDate = null where SequenceOrderStepID = @SequenceOrderStepID
		end
		else
		begin
			-- Update the CurrentCount for the current step in the sequence.
			update tblSequenceOrderStep 
			set NextTriggerDate = DATEADD(day, RecurrenceTerm, NextTriggerDate),
				CurrentCount = coalesce(CurrentCount, 1) + 1
			where SequenceOrderStepID = @SequenceOrderStepID
		end

		fetch next from SequenceOrderStepCursor into @SequenceOrderStepID, @RecurrenceCount, @CurrentCount, @SequenceOrderID
	end
	close SequenceOrderStepCursor
	deallocate SequenceOrderStepCursor

	-- Set the status to complete for any active sequences that have no more steps to complete.
	update T1 set SequenceStatusCode = 'C'
	from tblSequenceOrder T1
	where SequenceStatusCode = 'A'
		and not exists ( select 1 from tblSequenceOrderStep where SequenceOrderID = T1.SequenceOrderID and NextTriggerDate is not null )

RETURN 0